package edu.wtbu.herper;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSetMetaData;


public class MysqlHelper {

	
	private static PreparedStatement ps=null;
	private static ResultSet rs=null;
	private static Connection ct=null;
	
	private static String url=null;
	private static String driver=null;
	private static String username=null;
	private static String passwd=null;
	
	
	static {
		try {
			url="jdbc:mysql://localhost:3306/session1?characterEncoding=utf-8";
			driver="com.mysql.jdbc.Driver";
			username="root";
			passwd="123456";
			Class.forName(driver);
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	
	public static Connection getConnection() {
		try {
			ct=(Connection) DriverManager.getConnection(url,username,passwd);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return ct;
	}
	
	public static ArrayList<Object[]> executeQuery(String sql,Object[] parameters) {
		
		ArrayList<Object[]> list=null;
		try {
			ct=getConnection();
			ps=(PreparedStatement) ct.prepareStatement(sql);
			if(parameters!=null)
			{
				for(int i=0;i<parameters.length;i++)
				{
					String className=parameters[i].getClass().getName();
					if(className.contains("String"))
					{
						ps.setString(i+1, parameters[i].toString());
					}
					if(className.contains("Integer"))
					{
						ps.setInt(i+1, Integer.parseInt(parameters[i].toString()));
					}
				}
			}
			
			rs=ps.executeQuery();
			ResultSetMetaData rsmd=(ResultSetMetaData) rs.getMetaData();
			list=new ArrayList<Object[]>();
			int cloumnnum=rsmd.getColumnCount();
			while (rs.next()) {
				Object[] objects=new Object[cloumnnum];
				for(int i=0;i<objects.length;i++)
				{
					objects[i]=rs.getObject(i+1);
				}
				list.add(objects);
				
			}
			
			return list;
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally {
			close(rs, ps, ct);
		}
		return null;
	}
	
	public static void executeUpdate(String sql,Object[] parameters) {
		
		
		try {
			ct=getConnection();
			ps=(PreparedStatement) ct.prepareStatement(sql);
			if(parameters!=null)
			{
				for(int i=0;i<parameters.length;i++)
				{
					String className=parameters[i].getClass().getName();
					if(className.contains("String"))
					{
						ps.setString(i+1, parameters[i].toString());
					}
					if(className.contains("Integer"))
					{
						ps.setInt(i+1, Integer.parseInt(parameters[i].toString()));
					}
				}
			}
			
		ps.executeUpdate();
			
		
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally {
			close(rs, ps, ct);
		}
	}
	public static ArrayList<HashMap<String, Object>> executeQueryReturnMap(String sql,Object[] parameters) {
		

		try {
			ct=getConnection();
			ps=(PreparedStatement) ct.prepareStatement(sql);
			if(parameters!=null)
			{
				for(int i=0;i<parameters.length;i++)
				{
					String className=parameters[i].getClass().getName();
					if(className.contains("String"))
					{
						ps.setString(i+1, parameters[i].toString());
					}
					if(className.contains("Integer"))
					{
						ps.setInt(i+1, Integer.parseInt(parameters[i].toString()));
					}
				}
			}
			
			rs=ps.executeQuery();
			ResultSetMetaData rsmd=(ResultSetMetaData) rs.getMetaData();
			ArrayList<HashMap<String, Object>>	list=new ArrayList<HashMap<String, Object>>();
			int cloumnnum=rsmd.getColumnCount();
			while (rs.next()) {
				HashMap<String, Object> map=new HashMap<String, Object>();
				for(int i=0;i<cloumnnum;i++)
				{
					String columnname=rsmd.getColumnName(i+1);
					Object object=rs.getObject(i+1);
					map.put(columnname+"", object);
				}
				
				
				list.add(map);
				
			}
			close(rs, ps, ct);
			return list;
		} catch (Exception e) {
			// TODO: handle exception
		}
		return null;
		
	}
	
	
	public static void close(ResultSet rs,PreparedStatement ps,Connection ct)
	{
		if(rs!=null)
		{
			try {
				rs.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
			rs=null;
		}
		if(ps!=null)
		{
			try {
				ps.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
			ps=null;
		}
		if(ct!=null)
		{
			try {
				ct.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
			ct=null;
		}
	}
}
